package com.freemarker.db;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;

public class JDBCUtil {

	public static String driverName = null;
	public static String username = null;
	public static String pwd = null;
	public static String url_str = null;
	public static String fileName = "resultDB.properties";

	public static void main(String[] args) throws Exception {
		List<Map> columnList = JDBCUtil.getListByMap(SpringUtil.getData("clazzPO"), new Object[] { "student" });
		printf(columnList);
	}

	public static void test() throws Exception {
		String sql = "select column_name, data_type, data_length, data_precision, data_scale from user_tab_columns where table_name = 'JOB_INFO'";
		List<Map> columnList = JDBCUtil.getListByMap(sql, null);
		System.out.println("size:" + columnList.size());
		for (int i = 0; i < columnList.size(); i++) {
			Map map = columnList.get(i);
			printf(map);
		}
	}

	public static void test2() throws Exception {
		String sql = "select * from all_tab_comments a where a.table_name = ?";
		String ss = JDBCUtil.getString(sql, new Object[] { "CODE_DEFINE" });
		System.out.println(ss);
	}

	public static void printf(Map map) throws Exception {
		Iterator iter2 = map.keySet().iterator();
		while (iter2.hasNext()) {
			Object key = iter2.next();
			Object value = map.get(key);
			System.out.print(key + ":" + value + " ,");
		}
		System.out.println("");
	}

	public static void printf(List list) throws Exception {
		for (int i = 0; i < list.size(); i++) {
			Map map = (Map) list.get(i);
			printf(map);
		}
	}

	public static int executSQL(String sql, Object[] params) throws Exception {
		int num = 0;
		Connection conn = getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {

			System.out.println("\n------------getString()---------------------------------------------");
			System.out.println("SQL : " + sql.replaceAll("\n", " ").replaceAll("\t", ""));
			System.out.println("参数 ： " + Arrays.toString(params));
			System.out.println("=====================================================================\n");

			ps = conn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int j = 0; j < params.length; j++) {
					ps.setObject((j + 1), params[j]);
				}
			}
			num = ps.executeUpdate();
		} catch (Exception e) {
			throw new Exception();
		} finally {
			closeAll(conn, ps, rs);
		}
		return num;
	}

	public static List<Map> getListByMap(String sql, Object[] params) throws Exception {
		List<Map> resultList = null;
		Connection conn = getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {

			System.out.println("\n-------------getListByMap()------------------------------------------");
			System.out.println("SQL : " + sql.replaceAll("\n", " ").replaceAll("\t", ""));
			System.out.println("参数 ： " + Arrays.toString(params));
			System.out.println("=====================================================================\n");

			ps = conn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			rs = ps.executeQuery();

			ResultSetMetaData data = rs.getMetaData();
			int coloumCount = data.getColumnCount();
			String[] coloumNames = new String[coloumCount];
			for (int i = 0; i < coloumCount; i++) {
				coloumNames[i] = data.getColumnName(i + 1);
			}
			resultList = new ArrayList<Map>();
			while (rs.next()) {
				Map map = new HashMap();
				for (int i = 0; i < coloumCount; i++) {
					map.put(coloumNames[i], rs.getObject(coloumNames[i]));
				}
				resultList.add(map);
			}
		} catch (SQLException e) {
			throw new Exception();
		} finally {
			closeAll(conn, ps, rs);
		}
		if (resultList != null && resultList.size() == 0) {
			resultList = null;
		}
		return resultList;
	}

	
	public static Map getMap(String sql, Object[] params) throws Exception {
		Connection conn = getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Map resultlMap = null;
		try {

			System.out.println("\n-------------getListByMap()------------------------------------------");
			System.out.println("SQL : " + sql.replaceAll("\n", " ").replaceAll("\t", ""));
			System.out.println("参数 ： " + Arrays.toString(params));
			System.out.println("=====================================================================\n");

			ps = conn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			rs = ps.executeQuery();

			ResultSetMetaData data = rs.getMetaData();
			int coloumCount = data.getColumnCount();
			String[] coloumNames = new String[coloumCount];
			for (int i = 0; i < coloumCount; i++) {
				coloumNames[i] = data.getColumnName(i + 1);
			}
			while (rs.next()) {
				resultlMap=new HashMap();
				for (int i = 0; i < coloumCount; i++) {
					resultlMap.put(coloumNames[i], rs.getObject(coloumNames[i]));
				}
			}
		} catch (SQLException e) {
			throw new Exception();
		} finally {
			closeAll(conn, ps, rs);
		}
		return resultlMap;
	}

	
	
	public static String getString(String sql, Object[] params) throws Exception {
		List<Map> resultList = null;
		Connection conn = getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = null;
		try {

			System.out.println("\n-----------getString()-----------------------------------------------");
			System.out.println("SQL : " + sql.replaceAll("\n", " ").replaceAll("\t", ""));
			System.out.println("参数 ： " + Arrays.toString(params));
			System.out.println("=====================================================================\n");

			ps = conn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			rs = ps.executeQuery();

			resultList = new ArrayList<Map>();
			if (rs.next()) {
				result = (String) rs.getObject(1);
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			closeAll(conn, ps, rs);
		}
		if (resultList != null && resultList.size() == 0) {
			resultList = null;
		}
		return result;
	}

	public static void loadConfig() throws Exception {
		try {
			Properties pro = new Properties();
			java.net.URL url = JDBCUtil.class.getClassLoader().getResource(fileName);
			InputStream in = (InputStream) url.getContent();
			pro.load(in);
			driverName = pro.getProperty("driverName");
			username = pro.getProperty("user");
			pwd = pro.getProperty("pass");
			url_str = pro.getProperty("url_str");

		} catch (Exception e) {
			throw new Exception();
		}
	}

	public static Connection getConnection() throws Exception {
		loadConfig();
		Class.forName(driverName);
		Connection conn = DriverManager.getConnection(url_str, username, pwd);
		return conn;
	}

	public static void closeAll(Connection conn, PreparedStatement ps, ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if (ps != null) {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}
